﻿<cfsilent>

	<cfset sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice") />
	<cfset depId = sessionAdvice.getUserProp("teacherDepartment") />
 
    <cfset sql = "  SELECT a.institute_id,a.institute_name 
					FROM t_institute a
					WHERE a.institute_id = :depId "/>
                    
    <cfset queryObj = new Query( datasource=application.dnsSlave ) />
    <cfset queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" )/>
    
    <cfset rs_department = queryObj.execute( sql=sql ).getResult()/>
     
    <cfset sql = " SELECT a.sbj_id,a.sbj_name,
						   a.sbj_english,c.institute_name,
						   a.level_code,b.learning_branch,
						   a.schooling_length,a.term_start,
						   a.sbj_short,a.subject_code 
					FROM t_subject a 
						 INNER JOIN t_learning_branch b ON b.b_id = a.b_id 
               			 INNER JOIN t_institute c ON a.institute_id = c.institute_id 
					WHERE a.institute_id = :depId 
						  AND
						  a.sbj_direction = :SubjectDirection 
					ORDER BY a.level_code ASC ,a.term_start ASC ,a.sbj_name ASC"/>
                    
    <cfset queryObj = new Query( datasource=application.dnsSlave ) />
    <cfset queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" )/>
    <cfset queryObj.addParam( name="SubjectDirection", value='0', cfsqltype="cf_sql_char" )/>
    
	<cfset rs_subject = queryObj.execute( sql=sql ).getResult()/>

	<cfset dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice") />

	<cfset temFile = GetTempDirectory() & createUUID() & ".xls" />
	<cfset downFile = URLEncodedFormat(rs_department.institute_name & "专业设置", "utf-8") />
	
	<cfset excel = getProperty("serviceFactory").getBean("spreadSheetObject") />
	
	<!--- 创建工作簿 --->
	<cfset spreadsheetObj = excel.SpreadSheetNew("专业信息", false) />
	
	<!--- 添加表头 --->
	<cfset excel.SpreadsheetAddrow(spreadsheetObj, "国家专业代码,专业,简称,英文,托管院系(部),培养层次,学科门类,学制,招生学期") />
	
	<cfset row = 1 />
	 
    <cfloop query="rs_subject" >

		<cfset row++ />
		
		<!--- 构建专业信息 --->
		<cfset rowData = "" 
						& "," 
						& rs_subject.sbj_name 
						& "," 
						& rs_subject.sbj_short
						& "," 
						& rs_subject.sbj_english
						& "," 
						& rs_subject.institute_name
						& "," 
						& dictAdvice.getStudentLevel(rs_subject.level_code)
						& "," 
						& rs_subject.learning_branch
						& "," 
						& rs_subject.schooling_length
						& "," 
						& dictAdvice.getTermEnrol(rs_subject.term_start)
						 />
		
		<!--- 写入专业信息 --->	
		<cfset excel.SpreadsheetAddrow(spreadsheetObj, rowData) />
		
		<!--- 格式化 学制单元格 使用一位小数 --->
		<cfset excel.SpreadsheetFormatCell(spreadsheetObj, {"dataformat"="0.0"}, row, 8) />

		<!--- 将第一列格式设置为文本 --->		
		<cfset excel.SpreadsheetFormatCell(spreadsheetObj, {"dataformat"="@"}, row, 1) />
		<!--- 将国家专业代码写入 --->		
		<cfset excel.SpreadsheetSetCellValue(spreadsheetObj, rs_subject.subject_code, row, 1) />
		
		<!--- 查找专业方向 ---> 
		<cfset sql = "  SELECT 
                               a.sbj_name,a.sbj_short,
                               a.sbj_english 
                        FROM t_subject a 
                        WHERE a.sbj_id LIKE :parent 
                        ORDER BY a.sbj_id ASC"/>
                        
        <cfset queryObj = new Query( datasource=application.dnsSlave ) />
        <cfset queryObj.addParam( name="parent", value=rs_subject.sbj_id & '_', cfsqltype="cf_sql_varchar" )/>
        
        <cfset rs_dir = queryObj.execute( sql=sql ).getResult()/>
		
		<!--- 如果有专业方向 ---> 
    		<cfloop query="rs_dir" >
		
			<cfset row++ />
			
			<!--- 构建专业方向信息 --->
			<cfset rowData = "" 
						& "," 
						& rs_dir.sbj_name 
						& "," 
						& rs_dir.sbj_short
						& "," 
						& rs_dir.sbj_english
						& "," 
						& ""
						& "," 
						& ""
						& "," 
						& ""
						& "," 
						& ""
						& "," 
						& ""
						 />
			
			<!--- 写入专业信息 --->	
			<cfset excel.SpreadsheetAddrow(spreadsheetObj, rowData) />
		
		</cfloop>
						
	</cfloop>

	<cfset excel.SpreadSheetWrite(spreadsheetObj, temFile, true) />

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>